/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.bld.util.facade;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author Pedro
 */
public abstract class AbstractDao {

    private final Connection conn;
    private final EntityFactory entityFactory;

    public AbstractDao(Connection conn, EntityFactory entityFactory) {
        this.conn = conn;
        this.entityFactory = entityFactory;
    }

    public AbstractDao(Connection conn) {
        this.conn = conn;
        this.entityFactory = null;
    }

    public PreparedStatement getPreparedStatement(String sql) throws SQLException {

        return conn.prepareStatement(sql);
    }

    public ResultSet getResultSet(PreparedStatement ps) throws SQLException {

        return ps.executeQuery();
    }

    public int executeUpdate(PreparedStatement ps) throws SQLException {

        return ps.executeUpdate();
    }

    public void close(PreparedStatement ps) throws SQLException {

        if (ps != null) {
            ps.close();
        }

    }

    public void close(ResultSet rs) throws SQLException {

        if (rs != null) {
            rs.close();
        }

    }

    public void close(PreparedStatement ps, ResultSet rs) throws SQLException {
        if (ps != null) {
            ps.close();
        }

        if (rs != null) {
            rs.close();
        }
    }

    public void close(ResultSet rs, PreparedStatement ps) throws SQLException {
        if (ps != null) {
            ps.close();
        }

        if (rs != null) {
            rs.close();
        }
    }

    public String createInsert() {
        StringBuilder sql = new StringBuilder();

        sql.append("INSERT INTO\n");
        sql.append(entityFactory.getSchema()).append(".").append(entityFactory.getTable()).append("\n");
        sql.append("(\n");
        sql.append(entityFactory.getFieldsFormatatedToInsert()).append("\n");
        sql.append(")\n");
        sql.append("VALUES(\n");
        sql.append(entityFactory.getFieldsParm()).append("\n");
        sql.append(")\n");

        return sql.toString();
    }

    public String createUpdate() {
        StringBuilder sql = new StringBuilder();

        sql.append("UPDATE\n");
        sql.append(entityFactory.getSchema()).append(".").append(entityFactory.getTable()).append("\n");
        sql.append("SET\n");
        sql.append(entityFactory.getFieldsFormatatedToUpdate()).append("\n");
        sql.append("WHERE\n");
        sql.append(entityFactory.getPKFormatatedToWhere());

        return sql.toString();
    }

    public String createDelete() {
        StringBuilder sql = new StringBuilder();

        sql.append("DELETE FROM\n");
        sql.append(entityFactory.getSchema()).append(".").append(entityFactory.getTable()).append("\n");
        sql.append("WHERE\n");
        sql.append(entityFactory.getPKFormatatedToWhere());

        return sql.toString();
    }

    public String createSelect() {
        StringBuilder sql = new StringBuilder();

        sql.append("SELECT * FROM ").append(entityFactory.getSchema()).append(".").append(entityFactory.getTable()).append("\n");

        return sql.toString();
    }

    public String createSelectByPk() {
        StringBuilder sql = new StringBuilder();

        sql.append("SELECT * FROM ").append(entityFactory.getSchema()).append(".").append(entityFactory.getTable()).append("\n");
        sql.append("WHERE\n");
        sql.append(entityFactory.getPKFormatatedToWhere());

        return sql.toString();
    }

    public String createSelectByPage(Integer numrPagina, Integer qtdePorPagina) {
        StringBuilder sql = new StringBuilder();

        sql.append("SELECT * FROM\n");
        sql.append(entityFactory.getSchema()).append(".").append(entityFactory.getTable()).append("\n");
        sql.append("ORDER BY\n");
        sql.append(entityFactory.getOrderByAsc()).append("\n");
        sql.append("LIMIT ").append(qtdePorPagina).append(" OFFSET ").append(qtdePorPagina * numrPagina);

        return sql.toString();
    }

    public Long nextSequeceValue() throws SQLException {

        Long resposta = null;

        if (entityFactory.getSequence() == null || entityFactory.getSequence().equals("")) {
            return resposta;
        }

        StringBuilder sql = new StringBuilder();

        sql.append("SELECT NEXTVAL( '").append(entityFactory.getSchema()).append(".").append(entityFactory.getSequence()).append("' )");

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();

            if (rs.next()) {
                resposta = rs.getLong(1);
            }

            return resposta;

        } finally {
            if (ps != null) {
                ps.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
    }

    public String createSelectByKey(String[] key) {
        StringBuilder str = new StringBuilder();
        StringBuilder sql = new StringBuilder();

        for (String k : key) {
            str.append(k).append(" = ?, ");
        }

        sql.append("SELECT * FROM ").append(entityFactory.getSchema()).append(".").append(entityFactory.getTable()).append("\n");
        sql.append("WHERE\n");
        sql.append(str.toString().substring(0, str.length() - 2));

        return sql.toString();
    }

    public String createSelectDistinct(String[] key) {
        StringBuilder sql = new StringBuilder();
        StringBuilder campos = new StringBuilder();
        
        for(String k: key) {
            campos.append(k).append(", ");
        }
        
        sql.append("SELECT DISTINCT(\n");
        sql.append(campos.toString().substring(0, campos.length() - 2));
        sql.append(")\n");
        sql.append("FROM\n").append(entityFactory.getSchema()).append(".").append(entityFactory.getTable()).append("\n");

        return sql.toString();
    }

    public String createSelectByEntity() {
        StringBuilder sql = new StringBuilder();

        sql.append("SELECT * FROM ").append(entityFactory.getSchema()).append(".").append(entityFactory.getTable()).append("\n");
        sql.append("WHERE\n");
        sql.append(entityFactory.getWhereFields());

        return sql.toString();
    }
    
    public String createOrderByKeyAsc(String [] key) {
        StringBuilder sql = new StringBuilder();
        
        sql.append("ORDER BY\n");
        
        StringBuilder campos = new StringBuilder();
        
        for(String k: key) {
            campos.append(k).append(" ASC, ");
        }
        
        sql.append(campos.toString().substring(0, campos.length() - 2));
        
        return sql.toString();
    }
    
        public String createOrderByKeyDesc(String [] key) {
        StringBuilder sql = new StringBuilder();
        
        sql.append("ORDER BY\n");
        
        StringBuilder campos = new StringBuilder();
        
        for(String k: key) {
            campos.append(k).append(" DESC, ");
        }
        
        sql.append(campos.toString().substring(0, campos.length() - 2));
        
        return sql.toString();
    }
}
